Data Mining Extensions (DMX) Reference by Reference

Author:Reference
Language: eng
Format: mobi, epub
Publisher: Microsoft
Published: 2012-06-20T16:00:00+00:00

SELECT FROM <structure>.CASES

Returns the cases that were used to create the mining structure.

If drillthrough is not enabled on the structure, the statement will fail. Also, the statement will fail if the user does not have drillthrough permissions on the mining structure.

In Analysis Services, drillthrough on new mining structures is enabled by default. To verify whether drillthrough is enabled for a particular structure, check whether the value of the CacheMode property is set to KeepTrainingCases.

If the value of CacheMode is changed to ClearAfterProcessing, the structure cases are cleared from the cache and you cannot use drillthrough.

Note

You cannot enable or disable drillthrough on the mining structure by using Data Mining Extensions (DMX).

Syntax

SELECT [TOP n] <expression list> FROM <structure>.CASES [WHERE <condition expression>][ORDER BY <expression> [DESC|ASC]]

Click here to view code as image

Arguments

n

Optional. An integer that specifies how many rows to return.

expression list

A comma-separated list of expressions.

An expression can include column identifiers, user-defined functions, and VBA functions.

structure

The name of the structure.

condition expression

A condition to restrict the values that are returned from the column list.

expression

Optional. An expression that returns a scalar value.

Remarks

If drillthrough is enabled on both the model and the structure, any member of a role that has drillthrough permissions on the mining structure and the model can return structure columns that were not included in the model, by using the following syntax:

SELECT StructureColumn('<column name>') FROM <model>.CASES

Click here to view code as image

Therefore, to protect sensitive data or personal information, you should construct your data source view to mask personal information, and grant AllowDrillthrough permission on a mining structure or mining model only when necessary.

Examples

The following examples are based on the mining structure, Targeted Mailing, which is based on the Adventure Works DW Multidimensional 2012 database, and the associated mining models. For more information, see Basic Data Mining Tutorial.

Example 1: Drill through to Structure Cases

The following example returns a list of the 500 oldest customers in the mining structure, Targeted Mailing. The query returns all the columns in the mining model, but restricts the rows to those who purchased a bike, and orders them by age. You can also edit the expression list to return only the columns that you need.

SELECT TOP 500 * FROM [Targeted Mailing].Cases WHERE [Bike Buyer] = 1 ORDER BY Age DESC;

Click here to view code as image

Example 2: Drillthrough to Test or Training Cases Only

The following example returns a list of the structure cases for Targeted Mailing that are reserved for testing. If the mining structure does not contain a holdout test set, by default all cases are treated as training cases, and this query would return 0 cases.

SELECT [Customer Key], Gender, Age FROM [Targeted Mailing].Cases WHERE IsTestCase();

Click here to view code as image

To return the training cases, substitute the function IsTrainingCase().

See Also

SELECT (DMX)

Data Mining Extensions (DMX) Data Definition Statements

Data Mining Extensions (DMX) Data Manipulation Statements

Data Mining Extensions (DMX) Statement Reference

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.